<?php
/*
 * author: Mykolas
 * Pasiima duomenis iš pradinio xls failo ir juos įrašo į duombazę.
 * Apie savo būseną informuoja sėkmės/perspėjimų/klaidų pranešimais
 */
error_reporting(E_ALL);

if(!isset($_FILES['dataFile']['tmp_name']) || $_FILES['dataFile']['tmp_name'] == ''){
    return;
}

$inputFileName = $_FILES['dataFile']['tmp_name'];

$subdivisions = Array();
$information_systems = Array();
$support = Array();
$directions = Array();
$is__subdivisions = Array();
$support_administration = Array();
$historical_quantities = Array();

date_default_timezone_set('Europe/Vilnius');

require_once 'Plugins/PhpExcel/PHPExcel/IOFactory.php';


$objReader = PHPExcel_IOFactory::createReader("Excel5");
$objReader->setReadDataOnly(true);
try{
    $objPHPExcel = $objReader->load($inputFileName);
}
catch (Exception $e){
    add_error("Nurodytas failas \"".$_FILES['dataFile']['name']."\" yra netinkamo tipo. Apie tai kokius failus leidžiama importuoti galite pasiskaityti pagalbos skiltyje");
    return;
}

$processed_sheets = 0;

if (($sheet = $objPHPExcel->getSheetByName('Padaliniai')) != null) {
    process_subdivision_sheet($sheet);
    save_subdivisions_to_db();
    $processed_sheets++;
}
if (($sheet = $objPHPExcel->getSheetByName('IS')) != null) {
    process_is_sheet($sheet);
    save_is_to_db();
    $processed_sheets++;
}
if (($sheet = $objPHPExcel->getSheetByName('Paramos priemonės')) != null) {
    process_support_measures($sheet);
    save_support_to_db();
    save_directions_to_db();
    $processed_sheets++;
}
if (($sheet = $objPHPExcel->getSheetByName('IS-Padaliniai')) != null) {
    process_is__subdivisions($sheet);
    save_is__subdivisions_to_db();
    $processed_sheets++;
}
if (($sheet = $objPHPExcel->getSheetByName('Paramos administravimas')) != null) {
    process_support_administration($sheet);
    save_support_administration_to_db();
    $processed_sheets++;
}
if (($sheet = $objPHPExcel->getSheetByName('Paramos kiekiai')) != null) {
    process_support_quantities($sheet);
    save_quantities_to_db();
    $processed_sheets++;
}

if($processed_sheets > 0){
    add_success("Failas \"".$_FILES['dataFile']['name']."\" sėkmingai suimportuotas");
}
else{
    add_error("Faile \"".$_FILES['dataFile']['name']."\" nerasta nė viena importavimui tinkama kortelė.");
}

function process_subdivision_sheet(PHPExcel_Worksheet $sheet) {
    global $subdivisions;
    for ($row = 2; ($subdivisions[$row - 2]['code'] = $sheet->getCellByColumnAndRow(0, $row)->getValue()) != ''; $row++)
        $subdivisions[$row - 2]['title'] = $sheet->getCellByColumnAndRow(1, $row)->getValue();
    unset($subdivisions[$row - 2]);
}
function process_is_sheet(PHPExcel_Worksheet $sheet) {
    global $information_systems;
    for ($row = 2; ($information_systems[$row - 2]['code'] = $sheet->getCellByColumnAndRow(0, $row)->getValue()) != ''; $row++)
        $information_systems[$row - 2]['title'] = $sheet->getCellByColumnAndRow(1, $row)->getValue();
    unset($information_systems[$row - 2]);
}
function process_support_measures(PHPExcel_Worksheet $sheet) {
    global $support;
    global $directions;
    $row = 2;
    $last_direction_id = 0;

    while (true) {
        $code = $sheet->getCellByColumnAndRow(0, $row)->getValue();
        if ($code == '') {
            if ($sheet->getCellByColumnAndRow(0, $row + 1)->getValue() != '') { //Aprašoma nauja kryptis
                $direction = $sheet->getCellByColumnAndRow(1, $row)->getValue();
                if (!preg_match("/(\d+)\sKRYPTIS\s(.+)/", $direction, $matches)) {
                    add_warning('Klaida krypties eilutės struktūroje. Lentelė: ' . $sheet->getTitle() . " eilutė: " . $row . " Eilutė praleista.");
                    continue;
                }
                array_push($directions, Array('id' => $matches[1], 'title' => $matches[2]));
                $last_direction_id = $matches[1];
            }
            else
                break; //Pasibaigė duomenys
        }
        else {
            $support[$row - 2]['code'] = $code;
            if (!preg_match("/(\d+)\.\s(.+)/", $sheet->getCellByColumnAndRow(1, $row)->getValue(), $matches)) {
                add_warning('Klaida paramos priemonės eilutės struktūroje. Lentelė: ' . $sheet->getTitle() . " eilutė: " . $row . " Eilutė praleista.");
                continue;
            }
            $support[$row - 2]['title'] = $matches[2];
            $support[$row - 2]['direction_id'] = $last_direction_id;
        }
        $row++;
    }
}
function process_is__subdivisions(PHPExcel_Worksheet $sheet) {
    global $is__subdivisions;
    global $information_systems;
    global $subdivisions;

    //TODO: remiamasi jau surinktais duomenim apie is ir padalinius, todėl neveiks jei IS-Padaliniai kortelė bus pvz pirmoji
    for ($row = 4; $row < 4 + count($information_systems); $row++)
        for ($column = 1; $column < 1 + count($subdivisions); $column++) {
            $value = $sheet->getCellByColumnAndRow($column, $row)->getValue();
            if ($value != '')
                array_push($is__subdivisions, Array(
                    'is_code' => $sheet->getCellByColumnAndRow(0, $row)->getValue(),
                    'subdivision_code' => $sheet->getCellByColumnAndRow($column, 3)->getValue()
                ));
        }
}
function process_support_administration(PHPExcel_Worksheet $sheet) {
    global $support_administration;
    $subdivisions_codes = Array();
    $support_measures_codes = Array();

    for ($column = 1; ($newValue = $sheet->getCellByColumnAndRow($column, 3)->getValue()) != ''; $column++) {
        if ($newValue != 'Iš viso laiko') {
            array_push($subdivisions_codes, $newValue);
        }
    }
    for ($row = 4; ($newValue = $sheet->getCellByColumnAndRow(0, $row)->getValue()) != ''; $row++) {
        array_push($support_measures_codes, $newValue);
    }
    for ($row = 4; $row < 4 + count($support_measures_codes); $row++)
        for ($column = 1; $column < 1 + count($subdivisions_codes); $column++) {
            $time = $sheet->getCellByColumnAndRow($column, $row)->getValue();
            if ($time != '')
                array_push($support_administration, Array(
                    'subdivision_code' => $subdivisions_codes[$column - 1],
                    'measure_code' => $support_measures_codes[$row - 4],
                    'time' => $time
                ));
        }
}
function process_support_quantities(PHPExcel_Worksheet $sheet) {
    global $historical_quantities;
    for ($row = 2; ($historical_quantities[$row - 2]['measure_code'] = $sheet->getCellByColumnAndRow(0, $row)->getValue()) != ''; $row++) {
        $dateInExcelFormat = $sheet->getCellByColumnAndRow(1, $row)->getValue();
        $historical_quantities[$row - 2]['date'] = PHPExcel_Style_NumberFormat::toFormattedString($dateInExcelFormat, "yyyy-mm-dd");
        //FIXME: ar gerai kad gautų ir suadministruotų reikšmės vienodos?
        $historical_quantities[$row - 2]['received'] = $sheet->getCellByColumnAndRow(3, $row)->getValue();
        $historical_quantities[$row - 2]['administrated'] = $sheet->getCellByColumnAndRow(3, $row)->getValue();
    }
    unset($historical_quantities[$row - 2]);
}


function save_is_to_db(){
    global $information_systems;
    $sql_query = "INSERT INTO information_systems (code, title) VALUES";
    foreach ($information_systems as $is) {
        $sql_query .= sprintf(
                " ('%s', '%s'),", 
                mysql_real_escape_string($is['code']), 
                mysql_real_escape_string($is['title'])
        );
    }
    $sql_query = substr($sql_query, 0, strlen($sql_query)-1);
    $sql_query .= " ON DUPLICATE KEY UPDATE title=values(title)";
    if (($result = mysql_query($sql_query)) === false) {
        add_warning('Klaida įrašant informacines sistemas į duomenų bazę (' . mysql_error() . '). Duomenys apie informacines sistemas neįrašyti.');
    }
}
function save_subdivisions_to_db(){
    global $subdivisions;
    $sql_query = "INSERT INTO subdivisions (code, title) VALUES";
    foreach ($subdivisions as $subdivision) {
        $sql_query .= sprintf(
                " ('%s', '%s'),", 
                mysql_real_escape_string($subdivision['code']), 
                mysql_real_escape_string($subdivision['title'])
        );
    }
    $sql_query = substr($sql_query, 0, strlen($sql_query)-1);
    $sql_query .= " ON DUPLICATE KEY UPDATE title=values(title)";
    if (($result = mysql_query($sql_query)) === false) {
        add_warning('Klaida įrašant padalinius į duomenų bazę: ' . mysql_error() . '). Duomenys apie padalinius neįrašyti.');
    }
}
function save_support_to_db(){
    global $support;
    $sql_query = "INSERT INTO support(code, title, direction_id) VALUES";
    foreach ($support as $measure) {
        $sql_query .= sprintf(
                " ('%s', '%s', '%s'),", 
                mysql_real_escape_string($measure['code']), 
                mysql_real_escape_string($measure['title']), 
                mysql_real_escape_string($measure['direction_id'])
        );
    }
    $sql_query = substr($sql_query, 0, strlen($sql_query)-1);
    $sql_query .= " ON DUPLICATE KEY UPDATE title=values(title), direction_id=values(direction_id)";
    if (($result = mysql_query($sql_query)) === false) {
        add_warning('Klaida įrašant paramos priemones į duomenų bazę: ' . mysql_error() . '). Duomenys apie paramos priemones neįrašyti.');
    }
}
function save_is__subdivisions_to_db(){
    global $is__subdivisions;
    $sql_query = "INSERT INTO is_subdivisions (is_code, subdivision_code, date) VALUES";
    foreach ($is__subdivisions as $use) {
        $sql_query .= sprintf(
                " ('%s', '%s', '%s'),", 
                mysql_real_escape_string($use['is_code']), 
                mysql_real_escape_string($use['subdivision_code']),
                date("Y-m-d", time())
        );
    }
    $sql_query = substr($sql_query, 0, strlen($sql_query)-1);
    $sql_query .= " ON DUPLICATE KEY UPDATE is_code=values(is_code), subdivision_code=values(subdivision_code)";
    if (($result = mysql_query($sql_query)) === false) {
        add_warning('Klaida įrašant padalinių ir IS sąryšius į duomenų bazę: ' . mysql_error() . '). Duomenys apie padalinių ir is sąryšius neįrašyti.');
    }
}
function save_directions_to_db(){
    global $directions;
    $sql_query = "INSERT INTO directions (id, title) VALUES";
    foreach ($directions as $direction) {
        $sql_query .= sprintf(
                " ('%s', '%s'),", 
                mysql_real_escape_string($direction['id']), 
                mysql_real_escape_string($direction['title'])
        );
    }
    $sql_query = substr($sql_query, 0, strlen($sql_query)-1);
    $sql_query .= " ON DUPLICATE KEY UPDATE title=values(title)";
    if (($result = mysql_query($sql_query)) === false) {
        add_warning('Klaida įrašant paramos priemonių kryptis į duomenų bazę: ' . mysql_error() . '). Duomenys apie paramos priemonių kryptis neįrašyti.');
    }
}
function save_support_administration_to_db() {
    global $support_administration;
    $sql_query = "INSERT INTO support_administration (measure_code, subdivision_code, time, date) VALUES";
    foreach ($support_administration as $rec) {
        $sql_query .= sprintf(
                //FIXME: pakeisti 3 argumentą į realaus skaičiaus, kai netas pradės veikt
                " ('%s', '%s', %s, '%s'),", 
                mysql_real_escape_string($rec['measure_code']), 
                mysql_real_escape_string($rec['subdivision_code']), 
                mysql_real_escape_string($rec['time']),
                date("Y-m-d", time())
        );
    }
    $sql_query = substr($sql_query, 0, strlen($sql_query)-1);
    $sql_query .= " ON DUPLICATE KEY UPDATE time=values(time)";
    if (($result = mysql_query($sql_query)) === false) {
        add_warning('Klaida įrašant paramos administravimą į duomenų bazę: ' . mysql_error() . '). Duomenys apie paramos administravimą neįrašyti.');
    }
}
function save_quantities_to_db() {
    global $historical_quantities;
    $sql_query = "INSERT INTO historical_quantities (date, measure_code, received, administrated) VALUES";
    foreach ($historical_quantities as $record) {        
        $sql_query .= sprintf(
                " ('%s', '%s', %d, %d),", 
                mysql_real_escape_string($record['date']), 
                mysql_real_escape_string($record['measure_code']), 
                mysql_real_escape_string($record['received']), 
                mysql_real_escape_string($record['administrated'])
        );
    }
    $sql_query = substr($sql_query, 0, strlen($sql_query)-1);
    $sql_query .= " ON DUPLICATE KEY UPDATE received=values(received), administrated=values(administrated)";
    if (($result = mysql_query($sql_query)) === false) {
        add_warning('Klaida įrašant istorinius paraiškų kiekius į duomenų bazę: ' . mysql_error() . '). Duomenys apie istorinius paraiškų kiekius neįrašyti.');
    }
}
?>